﻿<DataListSpec 
	xmlns="bb_appfx_datalist"
	xmlns:common="bb_appfx_commontypes" 
	ID="49ea8758-8118-422a-8cc8-3b2f90f17a71" 
	Name="AddressesForConstituent Delete Data List"
	Description="Return AddressAudit Delete Records for Constituent." 
	Author="Blackbaud Professional Services"
	common:SecurityUIFolder="Service Bus Adapter">



    <!-- define the SP that returns the data for the datalist -->
    <SPDataList SPName="USP_DATALIST_ADDRESSESFORCONSTITUENTDELETE">
      <common:CreateProcedureSQL>
        <![CDATA[
create procedure dbo.USP_DATALIST_ADDRESSESFORCONSTITUENTDELETE(
  @CONSTITUENTID uniqueidentifier, @UPDATEDSINCE datetime)
as
	set nocount on;
  
SELECT
  a.AUDITRECORDID AS ID,
	a.CONSTITUENTID,
	at.DESCRIPTION  as ADDRESSTYPE,
	s.DESCRIPTION   as STATEFULLNAME,
	s.ABBREVIATION  as STATEABBREVIATEDNAME,
	c.DESCRIPTION   as COUNTRYFULLNAME, 
	c.ABBREVIATION  as COUNTRYABBREVIATEDNAME, 
	a.ISPRIMARY, 
	a.STARTDATE, 
	a.ENDDATE, 
	a.ADDRESSBLOCK, 
	a.CITY, 
	a.POSTCODE, 
  a.DONOTMAIL,
	a.ISCONFIDENTIAL,
  COALESCE(ax.LOOKUPID, ''),
  a.DATEADDED,
  a.DATECHANGED,
  ca.USERNAME as CHANGEDBYUSERNAME

  
FROM ADDRESSAUDIT a
	LEFT OUTER JOIN COUNTRY         c   ON a.COUNTRYID          = c.ID
	LEFT OUTER JOIN STATE           s   ON a.STATEID            = s.ID
	LEFT OUTER JOIN ADDRESSTYPECODE at  ON a.ADDRESSTYPECODEID  = at.ID
  LEFT JOIN USR_ADDRESS           ax  ON a.AUDITRECORDID      = ax.ID
  LEFT OUTER JOIN CHANGEAGENT     ca  ON ca.ID                = a.CHANGEDBYID
WHERE 
a.AUDITTYPECODE = 2 --DELETE
AND a.CONSTITUENTID = @CONSTITUENTID
AND a.DATECHANGED >= @UPDATEDSINCE  
  

			]]>
      </common:CreateProcedureSQL>
    </SPDataList>

    <!-- describe any parameters (other than the context ID) defined on the SP -->
    <Parameters>
      <common:FormMetaData>
        <common:FormFields>
          <common:FormField FieldID="CONSTITUENTID" Caption="Constituent ID" DataType="Guid"/>
          <common:FormField FieldID="UPDATEDSINCE" Caption="Changed Since" DataType="Date"/>
        </common:FormFields>
      </common:FormMetaData>
    </Parameters>

    <!-- define the output fields in the list -->
    <Output>
      <OutputFields>
        <!-- 0 -->
        <OutputField FieldID="ID" Caption="ID" DataType="Guid" IsHidden="true" />
        <!-- 1 -->
        <OutputField FieldID="CONSTITUENTID" Caption="Constituent ID" DataType="Guid" />
        <!-- 2 -->
        <OutputField FieldID="ADDRESSTYPE" Caption="Address Type" DataType="String" />
        <!-- 3 -->
        <OutputField FieldID="STATEFULLNAME" Caption="State Full Name" DataType="String" />
        <!-- 4 -->
        <OutputField FieldID="STATEABBREVIATEDNAME" Caption="State Abbreviated Name" DataType="String" />
        <!-- 5 -->
        <OutputField FieldID="COUNTRYFULLNAME" Caption="Country Full Name" DataType="String" />
        <!-- 6 -->
        <OutputField FieldID="COUNTRYABBREVIATEDNAME" Caption="Country Abbreviated Name" DataType="String" />
        <!-- 7 -->
        <OutputField FieldID="ISPRIMARY" Caption="Is Primary" DataType="Boolean" />
        <!-- 8 -->
        <OutputField FieldID="STARTDATE" Caption="Start Date" DataType="String" />
        <!-- 9 -->
        <OutputField FieldID="ENDDATE" Caption="End Date" DataType="String" />
        <!-- 10 -->
        <OutputField FieldID="ADDRESSBLOCK" Caption="Address Block" DataType="String" />
        <!-- 11 -->
        <OutputField FieldID="CITY" Caption="Ciy" DataType="String" />
        <!-- 12 -->
        <OutputField FieldID="POSTCODE" Caption="Post Code" DataType="String" />
        <!-- 13 -->
        <OutputField FieldID="DONOTMAIL" Caption="Do Not Mail" DataType="Boolean" />
        <!-- 14 -->
        <OutputField FieldID="ISCONFIDENTIAL" Caption="Is Confidential" DataType="Boolean" />
        <!-- 15 -->
        <OutputField FieldID="LOOKUPID" Caption="Lookup ID" DataType="String" />
        <!-- 16 -->
        <OutputField FieldID="DATEADDED" Caption="Date Added" DataType="Date" />
        <!-- 17 -->
        <OutputField FieldID="DATECHANGED" Caption="Date Changed" DataType="Date" />
        <!-- 18 -->
        <OutputField FieldID="CHANGEDBYUSERNAME" Caption="Changed by User Name" DataType="String" />
      </OutputFields>
    </Output>
  </DataListSpec>
